package com.meida.common.utils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;

import com.google.common.collect.Lists;
import com.meida.common.base.module.ExportField;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.util.CollectionUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.*;

/**
 * excel导出
 */
@Slf4j
public class ExcelUtils {
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);

    }

    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }

    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        defaultExport(list, fileName, response);
    }

    public static void exportExcel(String fileName, ExportParams exportParams, List<ExcelExportEntity> entities, List<Map<String, Object>> list, HttpServletResponse response) {
        defaultExport(fileName, exportParams, entities, list, response);
    }

    private static void defaultExport(String fileName, ExportParams exportParams, List<ExcelExportEntity> entities, List<Map<String, Object>> list, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, entities, list);
        if (workbook != null) ;
        downLoadExcel(fileName, response, workbook);
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        if (workbook != null) ;
        downLoadExcel(fileName, response, workbook);
    }

    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            log.error("excel异常:{}", e.getMessage());
        }
    }

    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null) ;
        downLoadExcel(fileName, response, workbook);
    }

    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            File file = new File(filePath);
            list = ExcelImportUtil.importExcel(file, pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IllegalArgumentException("模板不能为空");
        } catch (Exception e) {
            log.error("excel异常:{}", e.getMessage());
        }
        return list;
    }

    public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(inputStream, pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IllegalArgumentException("模板不能为空");
        } catch (Exception e) {
            log.error("excel异常:{}", e.getMessage());
        }
        return list;
    }

    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
        if (file == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IllegalArgumentException("excel文件不能为空");
        } catch (Exception e) {
            log.error("excel异常:{}", e.getMessage());
        }
        return list;
    }


    /**
     * description: 动态生成excel 列
     * create by heyanfeng at 2019-05-29 18:02
     *
     * @param ef
     * @param entities
     */
    public static void dynamicNewAddExcel(List<ExportField> ef, List<ExcelExportEntity> entities) {
        //单元格的excel 表头
        ef.forEach(item -> {
            //需要合并单元格的表头
            List<ExportField> children = item.getChildren();
            if (!CollectionUtils.isEmpty(children)) {
                ExcelExportEntity parent = new ExcelExportEntity(item.getKey().toString(), item.getName());
                List<ExcelExportEntity> entitiesChildren = Lists.newArrayList();
                children.forEach(e -> {
                    entitiesChildren.add(new ExcelExportEntity(e.getKey().toString(), e.getName(), 30));
                });
                parent.setNeedMerge(true);
                parent.setList(entitiesChildren);
                entities.add(parent);
            } else {
                entities.add(new ExcelExportEntity(item.getKey().toString(), item.getName(), 30));
            }
        });
    }


    /**
     * description: 根据属性名称 获取属性的值
     * create by heyanfeng at 2019-05-29 19:26
     *
     * @param fieldName
     * @param o
     * @return Object
     */
    public static Object getFieldValueByName(String fieldName, Object o) {
        try {
            String firstLetter = fieldName.substring(0, 1).toUpperCase();
            String getter = "get" + firstLetter + fieldName.substring(1);
            Method method = o.getClass().getMethod(getter, new Class[]{});
            Object value = method.invoke(o, new Object[]{});
            return value;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return null;
        }
    }

    /**
     * description: 组装excel 数据
     * create by heyanfeng at 2019-05-29 20:30
     *
     * @param ef
     * @param statisData
     * @return List<Map < String, Object>>
     */
    public static List<Map<String, Object>> dynamicListDataByKey(List<ExportField> ef, List<?> statisData) {
        //最终的数据
        List<Map<String, Object>> datas = new ArrayList<>();
        Map map;
        for (Object t : statisData) {
            map = new HashMap();
            for (int j = 0; j < ef.size(); j++) {
                List<ExportField> children = ef.get(j).getChildren();
                if (!CollectionUtils.isEmpty(children)) {
                    //遍历需要合并单元格的子列
                    traversechildren(map, t, children, ef.get(j).getName());
                } else if (StringUtils.isNotBlank(ef.get(j).getName())) {
                    map.put(ef.get(j).getName(), getFieldValueByName(ef.get(j).getName(), t));
                }
            }
            datas.add(map);
        }
        return datas;
    }

    /**
     * description: 遍历需要合并单元格的子列
     * create by heyanfeng at 2019-05-31 14:19
     */
    private static void traversechildren(Map map, Object t, List<ExportField> children, String entityAttrName) {
        ArrayList<Map<String, Object>> childrenMaps = Lists.newArrayList();
        Map<String, Object> childrenMap = new HashMap();
        for (int k = 0; k < children.size(); k++) {
            if (StringUtils.isNotBlank(children.get(k).getName())) {
                childrenMap.put(children.get(k).getName(), getFieldValueByName(children.get(k).getName(), t));
            }
        }
        childrenMaps.add(childrenMap);
        map.put(entityAttrName, childrenMaps);
    }


    /**
     * description: 组装sql 查询条件
     * create by heyanfeng at 2019-05-30 10:09
     */
    public static String getExcelSql(List<ExportField> exportField) {
        String fileds;
        StringBuilder stringBuilder = new StringBuilder();
        exportField.forEach(item -> {
            //如果存在需要合并表头的列 则遍历
            List<ExportField> children = item.getChildren();
            if (!CollectionUtils.isEmpty(children)) {
                children.forEach(e -> {
                    if (StringUtils.isNotBlank(e.getKey().toString())) {
                        stringBuilder.append(e.getKey().toString() + "_");
                    }
                });
            } else if (StringUtils.isNotBlank(item.getKey().toString())) {
                stringBuilder.append(item.getKey().toString() + "_");
            }
        });
        stringBuilder.deleteCharAt(stringBuilder.lastIndexOf("_"));
        fileds = stringBuilder.toString();
        return fileds;
    }

    /**
     * description: 组装sql 查询条件
     * create by heyanfeng at 2019-05-30 10:09
     */
    public static String getGroupBySql(List<ExportField> exportField) {
        String fileds;
        //是否加入 group by
        Boolean[] flag = {false};
        StringBuilder stringBuilder = new StringBuilder();
        exportField.forEach(item -> {
            if (item.getGroupBy() == null && StringUtils.isNotBlank(item.getKey().toString())) {
                stringBuilder.append(item.getKey().toString() + "_");
            } else if (item.getGroupBy() != null && item.getGroupBy() == 1) {
                flag[0] = true;
            }
        });
        stringBuilder.deleteCharAt(stringBuilder.lastIndexOf("_"));
        fileds = stringBuilder.toString();
        if (flag[0]) {
            fileds = " group by " + fileds;
            return fileds;
        }
        return "";
    }


    /**
     * description: 检查导出字段是否为空
     * create by heyanfeng at 2019-05-30 10:10
     */
    public static void checkExportField(List<ExportField> exportField) {
        if (CollectionUtils.isEmpty(exportField)) {
            throw new IllegalArgumentException("导出的字段不能为空");
        }
    }

    /**
     * description: 动态导出生成excel
     * create by heyanfeng at 2019-05-30 10:37
     */
    public static void DynamicExcel(String fileName, String title, String sheet, List<ExportField> exportField, HttpServletResponse response, List<?> list) {
        //生成动态列
        List<ExcelExportEntity> entities = Lists.newArrayList();
        ExcelUtils.dynamicNewAddExcel(exportField, entities);
        //组装数据entities
        List<Map<String, Object>> maps = ExcelUtils.dynamicListDataByKey(exportField, list);
        ExcelUtils.exportExcel(fileName, new ExportParams(title, sheet), entities, maps, response);
    }
}
